<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>MySQL加强 | 浪飞IT小栈</title>
    <meta name="generator" content="VuePress 1.9.9">
    <link rel="icon" href="/img/favicon.ico">
    <meta name="description" content="前后端技术分享">
    <meta name="keywords" content="前端博客,后端博客,人工智能,个人技术博客,前端,前端开发,前端框架,web前端,前端面试题,技术文档,学习,面试,JavaScript,js,ES6,TypeScript,vue,python,css3,html5,Node,git,github,markdown,java,Spring,Springboot,Redis,mysql">
    <meta name="baidu-site-verification" content="7F55weZDDc">
    <meta name="theme-color" content="#11a8cd">
    
    <link rel="preload" href="/assets/css/0.styles.ada71c49.css" as="style"><link rel="preload" href="/assets/js/app.a2a00aa5.js" as="script"><link rel="preload" href="/assets/js/2.256f807a.js" as="script"><link rel="preload" href="/assets/js/47.edf3ddf8.js" as="script"><link rel="prefetch" href="/assets/js/10.ab144fe3.js"><link rel="prefetch" href="/assets/js/11.062e0879.js"><link rel="prefetch" href="/assets/js/12.f9c60882.js"><link rel="prefetch" href="/assets/js/13.0a6ebfcf.js"><link rel="prefetch" href="/assets/js/14.03fc6f12.js"><link rel="prefetch" href="/assets/js/15.19dbf9fd.js"><link rel="prefetch" href="/assets/js/16.6e68160b.js"><link rel="prefetch" href="/assets/js/17.1d678834.js"><link rel="prefetch" href="/assets/js/18.4536fd5c.js"><link rel="prefetch" href="/assets/js/19.9c1b65df.js"><link rel="prefetch" href="/assets/js/20.c5f96e71.js"><link rel="prefetch" href="/assets/js/21.3f0b334e.js"><link rel="prefetch" href="/assets/js/22.55c868be.js"><link rel="prefetch" href="/assets/js/23.91286241.js"><link rel="prefetch" href="/assets/js/24.c1af1909.js"><link rel="prefetch" href="/assets/js/25.a995a755.js"><link rel="prefetch" href="/assets/js/26.abc5dea3.js"><link rel="prefetch" href="/assets/js/27.1eb62850.js"><link rel="prefetch" href="/assets/js/28.b383a871.js"><link rel="prefetch" href="/assets/js/29.088e0d73.js"><link rel="prefetch" href="/assets/js/3.cd716564.js"><link rel="prefetch" href="/assets/js/30.3ccc98d5.js"><link rel="prefetch" href="/assets/js/31.aab7cc23.js"><link rel="prefetch" href="/assets/js/32.5e16b7e6.js"><link rel="prefetch" href="/assets/js/33.4aaeb741.js"><link rel="prefetch" href="/assets/js/34.45e8e898.js"><link rel="prefetch" href="/assets/js/35.5899c97a.js"><link rel="prefetch" href="/assets/js/36.32e2e263.js"><link rel="prefetch" href="/assets/js/37.c4e27bbd.js"><link rel="prefetch" href="/assets/js/38.fe5e60d2.js"><link rel="prefetch" href="/assets/js/39.2cb0b4ee.js"><link rel="prefetch" href="/assets/js/4.edf4d5e7.js"><link rel="prefetch" href="/assets/js/40.08d5bead.js"><link rel="prefetch" href="/assets/js/41.2f80f24b.js"><link rel="prefetch" href="/assets/js/42.43a49913.js"><link rel="prefetch" href="/assets/js/43.08da7ec2.js"><link rel="prefetch" href="/assets/js/44.7c7cb8a9.js"><link rel="prefetch" href="/assets/js/45.0ddf7e7f.js"><link rel="prefetch" href="/assets/js/46.7c4609a5.js"><link rel="prefetch" href="/assets/js/48.569a90b9.js"><link rel="prefetch" href="/assets/js/49.fa368ac8.js"><link rel="prefetch" href="/assets/js/5.24054156.js"><link rel="prefetch" href="/assets/js/50.b9b3ea7f.js"><link rel="prefetch" href="/assets/js/51.a2ebf0fe.js"><link rel="prefetch" href="/assets/js/52.31f02238.js"><link rel="prefetch" href="/assets/js/53.4110ef1e.js"><link rel="prefetch" href="/assets/js/54.dab741c7.js"><link rel="prefetch" href="/assets/js/55.f27984e9.js"><link rel="prefetch" href="/assets/js/56.bd4c1a8e.js"><link rel="prefetch" href="/assets/js/57.375d40cb.js"><link rel="prefetch" href="/assets/js/58.932e3864.js"><link rel="prefetch" href="/assets/js/59.e657be1b.js"><link rel="prefetch" href="/assets/js/6.b259c061.js"><link rel="prefetch" href="/assets/js/60.88d94274.js"><link rel="prefetch" href="/assets/js/61.fa71d884.js"><link rel="prefetch" href="/assets/js/62.a9d3b3b7.js"><link rel="prefetch" href="/assets/js/63.f849fe75.js"><link rel="prefetch" href="/assets/js/64.1770f7a9.js"><link rel="prefetch" href="/assets/js/65.6afc33db.js"><link rel="prefetch" href="/assets/js/66.799f2eb4.js"><link rel="prefetch" href="/assets/js/67.f36b2f7d.js"><link rel="prefetch" href="/assets/js/68.868cd00b.js"><link rel="prefetch" href="/assets/js/69.4c29b907.js"><link rel="prefetch" href="/assets/js/7.e34ea00f.js"><link rel="prefetch" href="/assets/js/70.1b993ed4.js"><link rel="prefetch" href="/assets/js/71.e16ad4f9.js"><link rel="prefetch" href="/assets/js/72.627505e6.js"><link rel="prefetch" href="/assets/js/73.32768e14.js"><link rel="prefetch" href="/assets/js/74.968a580d.js"><link rel="prefetch" href="/assets/js/75.42d5af3d.js"><link rel="prefetch" href="/assets/js/76.19a69d31.js"><link rel="prefetch" href="/assets/js/77.cf627a78.js"><link rel="prefetch" href="/assets/js/78.07177880.js"><link rel="prefetch" href="/assets/js/8.c4dd00ce.js"><link rel="prefetch" href="/assets/js/9.0ff73be2.js">
    <link rel="stylesheet" href="/assets/css/0.styles.ada71c49.css">
  </head>
  <body class="theme-mode-light">
    <div id="app" data-server-rendered="true"><div class="theme-container sidebar-open have-rightmenu"><header class="navbar blur"><div title="目录" class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><img src="/img/logo.png" alt="浪飞IT小栈" class="logo"> <span class="site-name can-hide">浪飞IT小栈</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/" class="nav-link">首页</a></div><div class="nav-item"><a href="/pages/6138ae/" class="nav-link">Java速通</a></div><div class="nav-item"><a href="/pages/705b35/" class="nav-link">面试经典</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="精品项目" class="dropdown-title"><a href="/project/" class="link-title">精品项目</a> <span class="title" style="display:none;">精品项目</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>桌面端</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/pages/8143cc480faf9a11/" class="nav-link">JavaScript</a></li></ul></li><li class="dropdown-item"><h4>PC后端</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/note/javascript/" class="nav-link">项目1</a></li><li class="dropdown-subitem"><a href="/note/typescript-axios/" class="nav-link">项目二</a></li></ul></li></ul></div></div><div class="nav-item"><a href="/about/" class="nav-link">电子书</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/archives/" class="nav-link">归档</a></li></ul></div></div><div class="nav-item"><a href="/pages/0c07b2/" class="nav-link">IT杂货铺</a></div><div class="nav-item"><a href="/archives/" class="nav-link">更多资源</a></div> <!----></nav></div></header> <div class="sidebar-mask"></div> <div class="sidebar-hover-trigger"></div> <aside class="sidebar" style="display:none;"><div class="blogger"><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/avatar.jpg"> <div class="blogger-info"><h3>浪飞yes</h3> <span>无法简单的人儿~</span></div></div> <nav class="nav-links"><div class="nav-item"><a href="/" class="nav-link">首页</a></div><div class="nav-item"><a href="/pages/6138ae/" class="nav-link">Java速通</a></div><div class="nav-item"><a href="/pages/705b35/" class="nav-link">面试经典</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="精品项目" class="dropdown-title"><a href="/project/" class="link-title">精品项目</a> <span class="title" style="display:none;">精品项目</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>桌面端</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/pages/8143cc480faf9a11/" class="nav-link">JavaScript</a></li></ul></li><li class="dropdown-item"><h4>PC后端</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/note/javascript/" class="nav-link">项目1</a></li><li class="dropdown-subitem"><a href="/note/typescript-axios/" class="nav-link">项目二</a></li></ul></li></ul></div></div><div class="nav-item"><a href="/about/" class="nav-link">电子书</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/archives/" class="nav-link">归档</a></li></ul></div></div><div class="nav-item"><a href="/pages/0c07b2/" class="nav-link">IT杂货铺</a></div><div class="nav-item"><a href="/archives/" class="nav-link">更多资源</a></div> <!----></nav>  <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>开篇</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>基础语法</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>面向对象</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>常用类</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>基础高级</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>数据库</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>前端技术</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>热门框架</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/pages/71d587/" class="sidebar-link">Maven</a></li><li><a href="/pages/1b0493/" aria-current="page" class="active sidebar-link">MySQL加强</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level2"><a href="/pages/1b0493/#一、今日学习内容与目标" class="sidebar-link">一、今日学习内容与目标</a></li><li class="sidebar-sub-header level2"><a href="/pages/1b0493/#二、数据库三范式" class="sidebar-link">二、数据库三范式</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_2-1-三范式" class="sidebar-link">2.1 三范式</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_2-2-sql-分类" class="sidebar-link">2.2 SQL 分类</a></li><li class="sidebar-sub-header level4"><a href="/pages/1b0493/#_2-2-1-数据查询语言-dql" class="sidebar-link">2.2.1 数据查询语言（DQL）</a></li><li class="sidebar-sub-header level4"><a href="/pages/1b0493/#_2-2-2-数据定义语言-ddl" class="sidebar-link">2.2.2 数据定义语言（DDL）</a></li><li class="sidebar-sub-header level4"><a href="/pages/1b0493/#_2-2-3-数据操作语言-dml" class="sidebar-link">2.2.3 数据操作语言（DML）</a></li><li class="sidebar-sub-header level4"><a href="/pages/1b0493/#_2-2-4-事务处理语言-tcl" class="sidebar-link">2.2.4 事务处理语言（TCL）</a></li><li class="sidebar-sub-header level4"><a href="/pages/1b0493/#_2-2-5-数据库控制语言-dcl" class="sidebar-link">2.2.5 数据库控制语言（DCL）</a></li><li class="sidebar-sub-header level4"><a href="/pages/1b0493/#_2-2-6-指针控制语言-ccl" class="sidebar-link">2.2.6 指针控制语言（CCL）</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_2-3-练习的表结构" class="sidebar-link">2.3 练习的表结构</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/1b0493/#三、单表查询" class="sidebar-link">三、单表查询</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_3-1-全列和投影查询" class="sidebar-link">3.1 全列和投影查询</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_3-2-消除重复" class="sidebar-link">3.2 消除重复</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_3-3-算术运算符" class="sidebar-link">3.3 算术运算符</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_3-4-过滤查询" class="sidebar-link">3.4 过滤查询</a></li><li class="sidebar-sub-header level4"><a href="/pages/1b0493/#_3-4-1-过滤查询值的注意事项" class="sidebar-link">3.4.1 过滤查询值的注意事项</a></li><li class="sidebar-sub-header level4"><a href="/pages/1b0493/#_3-4-2-空值" class="sidebar-link">3.4.2 空值</a></li><li class="sidebar-sub-header level4"><a href="/pages/1b0493/#_3-4-3-常用算术比较运算符" class="sidebar-link">3.4.3 常用算术比较运算符</a></li><li class="sidebar-sub-header level4"><a href="/pages/1b0493/#_3-4-4-逻辑运算符" class="sidebar-link">3.4.4 逻辑运算符</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_3-5-结果排序" class="sidebar-link">3.5 结果排序</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/1b0493/#四、多表查询" class="sidebar-link">四、多表查询</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_4-1-为什么使用多表查询" class="sidebar-link">4.1 为什么使用多表查询</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_4-2-笛卡尔积" class="sidebar-link">4.2 笛卡尔积</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_4-3-多表查询分类" class="sidebar-link">4.3 多表查询分类</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/1b0493/#五、内连接查询" class="sidebar-link">五、内连接查询</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_5-1-隐式内连接查询" class="sidebar-link">5.1 隐式内连接查询</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_5-2-显式内连接查询" class="sidebar-link">5.2 显式内连接查询</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/1b0493/#六、外连接查询" class="sidebar-link">六、外连接查询</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_6-1-需求" class="sidebar-link">6.1 需求</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_6-2-左外连接查询" class="sidebar-link">6.2 左外连接查询</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_6-3-右外连接查询" class="sidebar-link">6.3 右外连接查询</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/1b0493/#七、分组函数" class="sidebar-link">七、分组函数</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_7-1-函数分类" class="sidebar-link">7.1 函数分类</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_7-2-多行函数" class="sidebar-link">7.2 多行函数</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/1b0493/#八、分组查询" class="sidebar-link">八、分组查询</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_8-1-什么是分组及其应用" class="sidebar-link">8.1 什么是分组及其应用</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_8-2-分组语法" class="sidebar-link">8.2 分组语法</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_8-3-使用分组注意" class="sidebar-link">8.3 使用分组注意</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_8-4-分组限定" class="sidebar-link">8.4 分组限定</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/1b0493/#九、单行函数" class="sidebar-link">九、单行函数</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_9-1-日期函数" class="sidebar-link">9.1 日期函数</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_9-2-日期转换为字符串" class="sidebar-link">9.2 日期转换为字符串</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/1b0493/#十、子查询" class="sidebar-link">十、子查询</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_10-1-定义和作用" class="sidebar-link">10.1 定义和作用</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_10-2-分类" class="sidebar-link">10.2 分类</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_10-3-单行单列" class="sidebar-link">10.3 单行单列</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_10-4-多行单列" class="sidebar-link">10.4 多行单列</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_10-5-多行多列" class="sidebar-link">10.5 多行多列</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/1b0493/#十一、小结与作业" class="sidebar-link">十一、小结与作业</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_11-1小结" class="sidebar-link">11.1小结</a></li><li class="sidebar-sub-header level3"><a href="/pages/1b0493/#_11-2-作业" class="sidebar-link">11.2 作业</a></li></ul></li></ul></li><li><a href="/pages/712f3f/" class="sidebar-link">MyBatis加强</a></li><li><a href="/pages/78eb56/" class="sidebar-link">MyBatis-Plus</a></li><li><a href="/pages/bbdeaa/" class="sidebar-link">Spring框架</a></li><li><a href="/pages/8bb4cd/" class="sidebar-link">SpringMVC框架</a></li><li><a href="/pages/4ab4d3/" class="sidebar-link">项目实战-SSM整合</a></li><li><a href="/pages/de0eaa/" class="sidebar-link">SpringBoot</a></li></ul></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>前后端分离</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>权限控制</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>NoSQL</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>微服务</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>消息中间件</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>脚手架</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>技术增值</span> <span class="arrow right"></span></p> <!----></section></li></ul> </aside> <div><main class="page"><div class="theme-vdoing-wrapper "><div class="articleInfo-wrap" data-v-06225672><div class="articleInfo" data-v-06225672><ul class="breadcrumbs" data-v-06225672><li data-v-06225672><a href="/" title="首页" class="iconfont icon-home router-link-active" data-v-06225672></a></li> <li data-v-06225672><a href="/categories/?category=Java%E9%80%9F%E9%80%9A" title="分类" data-v-06225672>Java速通</a></li><li data-v-06225672><a href="/categories/?category=%E7%83%AD%E9%97%A8%E6%A1%86%E6%9E%B6" title="分类" data-v-06225672>热门框架</a></li></ul> <div class="info" data-v-06225672><div title="作者" class="author iconfont icon-touxiang" data-v-06225672><a href="https://github.com/langfeiyes" target="_blank" title="作者" class="beLink" data-v-06225672>langfeiyes</a></div> <div title="创建时间" class="date iconfont icon-riqi" data-v-06225672><a href="javascript:;" data-v-06225672>2024-03-10</a></div> <!----></div></div></div> <!----> <div class="content-wrapper"><div class="right-menu-wrapper"><div class="right-menu-margin"><div class="right-menu-title">目录</div> <div class="right-menu-content"></div></div></div> <h1><img src="">MySQL加强<!----></h1>  <div class="theme-vdoing-content content__default"><h1 id="mysql加强"><a href="#mysql加强" class="header-anchor">#</a> MySQL加强</h1> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/mysql-logo.svg" alt=""></p> <h2 id="一、今日学习内容与目标"><a href="#一、今日学习内容与目标" class="header-anchor">#</a> 一、今日学习内容与目标</h2> <ul><li>理解数据库三范式。</li> <li>掌握使用单表查询。</li> <li>理解多表查询的作用，并掌握内连接查询和左外连接查询的使用。</li> <li>掌握使用分组函数。</li> <li>掌握使用分组查询，并注意查询列可以有哪些比较合理。</li> <li>了解单行函数。</li> <li>掌握子查询的使用。</li></ul> <h2 id="二、数据库三范式"><a href="#二、数据库三范式" class="header-anchor">#</a> 二、数据库三范式</h2> <h3 id="_2-1-三范式"><a href="#_2-1-三范式" class="header-anchor">#</a> 2.1 三范式</h3> <ul><li>第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库，是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值（一个列中只能存储一个值）；</li> <li>第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分（主键）；</li> <li>第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主键段信息。</li></ul> <h3 id="_2-2-sql-分类"><a href="#_2-2-sql-分类" class="header-anchor">#</a> 2.2 SQL 分类</h3> <h4 id="_2-2-1-数据查询语言-dql"><a href="#_2-2-1-数据查询语言-dql" class="header-anchor">#</a> 2.2.1 数据查询语言（DQL）</h4> <p>其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字 SELECT 是 DQL（也是所有 SQL）用得最多的动词，其他 DQL 常用的保留字有 WHERE，ORDER BY，GROUP BY 和 HAVING。这些 DQL 保留字常与其他类型的 SQL 语句一起使用。</p> <h4 id="_2-2-2-数据定义语言-ddl"><a href="#_2-2-2-数据定义语言-ddl" class="header-anchor">#</a> 2.2.2 数据定义语言（DDL）</h4> <p>其语句包括动词 CREATE 和 DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL 包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。</p> <h4 id="_2-2-3-数据操作语言-dml"><a href="#_2-2-3-数据操作语言-dml" class="header-anchor">#</a> 2.2.3 数据操作语言（DML）</h4> <p>其语句包括动词 INSERT，UPDATE 和 DELETE。它们分别用于添加，修改和删除表中的行，Insert / Update / Delete。也称为动作查询语言。</p> <h4 id="_2-2-4-事务处理语言-tcl"><a href="#_2-2-4-事务处理语言-tcl" class="header-anchor">#</a> 2.2.4 事务处理语言（TCL）</h4> <p>它的语句能确保被 DML 语句影响的表的所有行及时得以更新。TCL 语句包括 BEGIN TRANSACTION，COMMIT 和 ROLLBACK。</p> <h4 id="_2-2-5-数据库控制语言-dcl"><a href="#_2-2-5-数据库控制语言-dcl" class="header-anchor">#</a> 2.2.5 数据库控制语言（DCL）</h4> <p>它的语句通过 GRANT 或 REVOKE 获得许可，确定单个用户和用户组对数据库对象的访问。某些 RDBMS 可用 GRANT 或 REVOKE 控制对表单个列的访问。</p> <h4 id="_2-2-6-指针控制语言-ccl"><a href="#_2-2-6-指针控制语言-ccl" class="header-anchor">#</a> 2.2.6 指针控制语言（CCL）</h4> <p>它的语句，像 DECLARE CURSOR，FETCH INTO 和 UPDATE WHERE CURRENT 用于对一个或多个表单独行的操作。</p> <h3 id="_2-3-练习的表结构"><a href="#_2-3-练习的表结构" class="header-anchor">#</a> 2.3 练习的表结构</h3> <p>通过 navicat 导入 mysqlplus.sql 文件，查看表结构。</p> <p><em><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/tabledemostru.png" alt=""></em></p> <h2 id="三、单表查询"><a href="#三、单表查询" class="header-anchor">#</a> 三、单表查询</h2> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token punctuation">[</span><span class="token keyword">DISTINCT</span><span class="token punctuation">]</span> <span class="token operator">*</span> <span class="token operator">|</span> 字段 <span class="token punctuation">[</span>别名<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token punctuation">,</span> 字段 <span class="token punctuation">[</span>别名<span class="token punctuation">]</span><span class="token punctuation">]</span>
<span class="token keyword">FROM</span> 表名称 <span class="token punctuation">[</span>别名<span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">WHERE</span> 条件<span class="token punctuation">(</span>S<span class="token punctuation">)</span><span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> 字段 <span class="token punctuation">[</span><span class="token keyword">ASC</span><span class="token operator">|</span><span class="token keyword">DESC</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token punctuation">,</span> 字段 <span class="token punctuation">[</span><span class="token keyword">ASC</span><span class="token operator">|</span><span class="token keyword">DESC</span><span class="token punctuation">]</span><span class="token punctuation">,</span> …<span class="token punctuation">]</span><span class="token punctuation">]</span>
<span class="token keyword">LIMIT</span> ?<span class="token punctuation">,</span>?<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><h3 id="_3-1-全列和投影查询"><a href="#_3-1-全列和投影查询" class="header-anchor">#</a> 3.1 全列和投影查询</h3> <p>一个是查询表中所有列，另一个是查询表中部分的列。</p> <p>练习：</p> <ul><li>查询所有员工信息</li> <li>查询每个员工的编号、姓名、职位</li> <li>查询所有部门信息</li></ul> <h3 id="_3-2-消除重复"><a href="#_3-2-消除重复" class="header-anchor">#</a> 3.2 消除重复</h3> <p>DISTINCT 关键字可以用于一列，也可以用于多列。比如：SELECT distinct job,deptno FROM emp; 只有当 job 和 deptno 相同，才认为是重复的数据。</p> <p>练习：</p> <ul><li>查询所有有员工的部门编号</li> <li>查询所有员工的部门和职位</li></ul> <h3 id="_3-3-算术运算符"><a href="#_3-3-算术运算符" class="header-anchor">#</a> 3.3 算术运算符</h3> <ul><li>对 NUMBER 型数据可以使用算数操作符创建表达式（+  -  *  /）；</li> <li>对 DATE 型数据可以使用算数操作符创建表达式（+  -）。</li></ul> <p>练习：</p> <ul><li>查询所有员工的年薪</li> <li>查询所有员工的年薪（使用别名）</li></ul> <h3 id="_3-4-过滤查询"><a href="#_3-4-过滤查询" class="header-anchor">#</a> 3.4 过滤查询</h3> <h4 id="_3-4-1-过滤查询值的注意事项"><a href="#_3-4-1-过滤查询值的注意事项" class="header-anchor">#</a> 3.4.1 过滤查询值的注意事项</h4> <ul><li><strong>字符串和日期要用单引号扩起来</strong>；</li> <li><strong>数字类型直接书写</strong>；</li> <li>字符串是大小写不敏感的，日期值是格式大小写敏感的；</li> <li>字符串若要大小写敏感，需要添加 binary 关键字。（SELECT * FROM dept WHERE BINARY DNAME = 'sales'）</li></ul> <h4 id="_3-4-2-空值"><a href="#_3-4-2-空值" class="header-anchor">#</a> 3.4.2 空值</h4> <p>用 NULL 表示，注意：</p> <ul><li>空值是指不可用、未分配的值，也就是没有值；</li> <li><strong>空值不等于零或空格，也不表示空字符串</strong>；</li> <li><strong>任意类型都可以支持空值</strong>，也就是说任何类型的字段都可以允许空值作为值的存在；</li> <li><strong>包括空值的任何算术表达式都等于空</strong>。</li> <li>使用函数 IFNULL(expr1, expr2)，若 expr1 不是 NULL，IFNULL() 返回 expr1，否则它返回 expr2</li></ul> <h4 id="_3-4-3-常用算术比较运算符"><a href="#_3-4-3-常用算术比较运算符" class="header-anchor">#</a> 3.4.3 常用算术比较运算符</h4> <ul><li>=，!=，&lt;&gt;，&gt;，&gt;=，&lt;，&lt;=</li> <li>BETWEEN ... AND  ...：在两值之间 （包含开始和结尾，数学中的闭区间）</li> <li>IN：匹配列出的值；如：IN(1, 2, 3, 4)</li> <li>LIKE ：匹配字符串模式， _ 、%，LIKE 运算符必须使用通配符才有意义；
<ul><li>匹配单个字符：_ 表示 1 个；</li> <li>匹配任意多个字符：% 表示 0个、1个、多个。</li></ul></li> <li>IS NULL：是否为空</li></ul> <p>练习：</p> <ul><li><p>查询所有员工的年薪((月薪 + 奖金) * 12)</p></li> <li><p>查询有奖金的员工信息</p></li> <li><p>查询公司的老板</p></li> <li><p>查询出基本工资高于 1500 的所有员工信息</p></li> <li><p>查询名字叫 SCOTT 的员工所从事的工作</p></li> <li><p>查询 1981 年入职的员工信息</p></li> <li><p>查询年薪小于 3W 的员工</p></li> <li><p>查询所有不是销售人员的员工信息</p></li> <li><p>查询工资在 2000-3000 之间的员工信息</p></li> <li><p>查询工资为 800 或 1600 或 3000 的员工</p></li> <li><p>查询出所有雇员姓名是以 A 开头的全部雇员信息。</p></li> <li><p>查询出雇员姓名第二个字母是 M 的全部雇员信息。</p></li> <li><p>查询出雇员姓名任意位置上包含字母 A 的全部雇员信息。</p></li></ul> <h4 id="_3-4-4-逻辑运算符"><a href="#_3-4-4-逻辑运算符" class="header-anchor">#</a> 3.4.4 逻辑运算符</h4> <ul><li>AND：如果组合的条件都是 true，返回 true；</li> <li>OR：如果组合的条件 之一是 true ，返回 true；</li> <li>NOT：如果下面的条件是 false，返回 true。</li> <li>优先级规则：比较运算符 &gt; NOT &gt; AND &gt; OR。关键字查询会涉及到这一点</li></ul> <p>练习：</p> <ul><li>查询姓名中有 e 或者 a 的员工姓名</li> <li>查询工资在 1500~3000 之间的全部员工信息</li> <li>查询工资不在 2000-3000 之间的员工信息</li> <li>查询工资不为 800 或 1600 或 3000 的员工</li> <li>查询出职位是办事员 (CLERK) 或者是销售人员 (SALESMAN) 的全部信息，且工资在 1000 以上</li></ul> <h3 id="_3-5-结果排序"><a href="#_3-5-结果排序" class="header-anchor">#</a> 3.5 结果排序</h3> <ul><li>ASC：升序，缺省</li> <li>DESC：降序</li> <li>ORDER BY 子句出现在 SELECT 语句后执行；</li> <li>ORDER BY 可以使用别名，<strong>但不能使用加了引号的别名或列名来排序，没有效果</strong>。</li></ul> <p>练习：</p> <ul><li>查询所有员工信息，按照工资排序</li> <li>查询所有员工信息，按照年薪降序排序</li> <li>查询所有员工信息，按照部门和年薪降序排序</li></ul> <h2 id="四、多表查询"><a href="#四、多表查询" class="header-anchor">#</a> 四、多表查询</h2> <h3 id="_4-1-为什么使用多表查询"><a href="#_4-1-为什么使用多表查询" class="header-anchor">#</a> 4.1 为什么使用多表查询</h3> <p>需要查询的数据分散在多张表中，只有联合多张表才能查询出期望的数据。
比如：emp 表存储了员工的信息，dept 表存储了部门的信息，而需求是查询出员工的编号、姓名以及对应部门的名称。</p> <h3 id="_4-2-笛卡尔积"><a href="#_4-2-笛卡尔积" class="header-anchor">#</a> 4.2 笛卡尔积</h3> <ul><li>数学中：假设集合 A = {a, b}，集合 B = {0, 1, 2}，则两个集合的笛卡尔积为 {(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。</li> <li>MySQL 中：多表查询会产生笛卡尔积，比如：SELECT * FROM emp, dept，实际运行环境下，应避免使用全笛卡尔集。</li></ul> <p>解决方案：在 WHERE 语句中加入有效的连接条件 --&gt; 一般是等值连接，注意：连接 n 张表，至少需要 n-1 个连接条件。</p> <h3 id="_4-3-多表查询分类"><a href="#_4-3-多表查询分类" class="header-anchor">#</a> 4.3 多表查询分类</h3> <ul><li>内连接查询
<ul><li>隐式内连接查询</li> <li>显示内连接查询</li></ul></li> <li>外连接查询
<ul><li>左外连接查询</li> <li>右外连接查询</li> <li>全外连接查询</li></ul></li></ul> <p><em><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/SQL_JOINS.png" alt=""></em></p> <h2 id="五、内连接查询"><a href="#五、内连接查询" class="header-anchor">#</a> 五、内连接查询</h2> <h3 id="_5-1-隐式内连接查询"><a href="#_5-1-隐式内连接查询" class="header-anchor">#</a> 5.1 隐式内连接查询</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token punctuation">[</span><span class="token keyword">DISTINCT</span><span class="token punctuation">]</span> <span class="token operator">*</span> <span class="token operator">|</span> 字段 <span class="token punctuation">[</span>别名<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token punctuation">,</span> 字段 <span class="token punctuation">[</span>别名<span class="token punctuation">]</span><span class="token punctuation">,</span> …<span class="token punctuation">]</span>
<span class="token keyword">FROM</span> 表名称 <span class="token punctuation">[</span>别名<span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token punctuation">[</span>表名称 <span class="token punctuation">[</span>别名<span class="token punctuation">]</span><span class="token punctuation">,</span> …<span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">WHERE</span> 条件<span class="token punctuation">(</span>S<span class="token punctuation">)</span><span class="token operator">/</span>消除笛卡尔积连接<span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> 排序字段 <span class="token punctuation">[</span><span class="token keyword">ASC</span><span class="token operator">|</span><span class="token keyword">DESC</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token punctuation">,</span> 排序字段 <span class="token punctuation">[</span><span class="token keyword">ASC</span><span class="token operator">|</span><span class="token keyword">DESC</span><span class="token punctuation">]</span><span class="token punctuation">,</span> …<span class="token punctuation">]</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><ul><li>在 WHERE 子句中写入连接条件；</li> <li><strong>当多个表中有重名列时，必须在列的名字前加上表名作为前缀或者表的别名</strong>（使用别名更简单，性能更高）；</li> <li>等值连接是连接操作中最常见的一种，通常是在存在主外键约束条件的多表上建立的，连接条件中的两个字段通过等号建立等值关系。</li></ul> <p>练习：</p> <ul><li>查询员工编号，员工名称，员工所属部门的编号和名称</li> <li>查询员工的姓名，工资，所在部门的名称，以及工资的等级</li></ul> <h3 id="_5-2-显式内连接查询"><a href="#_5-2-显式内连接查询" class="header-anchor">#</a> 5.2 显式内连接查询</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> table1<span class="token punctuation">.</span><span class="token keyword">column</span><span class="token punctuation">,</span> table2<span class="token punctuation">.</span><span class="token keyword">column</span>
<span class="token keyword">FROM</span> table1 <span class="token punctuation">[</span><span class="token keyword">INNER</span><span class="token punctuation">]</span> <span class="token keyword">JOIN</span> table2 <span class="token keyword">ON</span> table1<span class="token punctuation">.</span>column1 <span class="token operator">=</span> table2<span class="token punctuation">.</span>column2
<span class="token keyword">WHERE</span> 条件
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>显示内连接查询：查询的结果和隐式内连接一模一样。区别在于：</p> <ul><li>显示内连接可以看到 [INNER]  JOIN；</li> <li>消除笛卡尔积条件使用写在 ON 子句。</li></ul> <h2 id="六、外连接查询"><a href="#六、外连接查询" class="header-anchor">#</a> 六、外连接查询</h2> <h3 id="_6-1-需求"><a href="#_6-1-需求" class="header-anchor">#</a> 6.1 需求</h3> <p>查询出员工的编号，名字，薪水和所在部门的名称（使用内连接查询），SQL 如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> emp<span class="token punctuation">.</span>EMPNO<span class="token punctuation">,</span> emp<span class="token punctuation">.</span>ENAME<span class="token punctuation">,</span> emp<span class="token punctuation">.</span>SAL<span class="token punctuation">,</span> dept<span class="token punctuation">.</span>DNAME <span class="token keyword">FROM</span> emp <span class="token keyword">JOIN</span> dept <span class="token keyword">ON</span> emp<span class="token punctuation">.</span>DEPTNO <span class="token operator">=</span> dept<span class="token punctuation">.</span>DEPTNO
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>执行完会发现，没有部门的员工则查询不出来。</p> <h3 id="_6-2-左外连接查询"><a href="#_6-2-左外连接查询" class="header-anchor">#</a> 6.2 左外连接查询</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> table1<span class="token punctuation">.</span><span class="token keyword">column</span><span class="token punctuation">,</span> table2<span class="token punctuation">.</span><span class="token keyword">column</span>
<span class="token keyword">FROM</span> table1 <span class="token keyword">LEFT</span> <span class="token punctuation">[</span><span class="token keyword">OUTER</span><span class="token punctuation">]</span> <span class="token keyword">JOIN</span> table2 <span class="token keyword">ON</span> table1<span class="token punctuation">.</span>column1 <span class="token operator">=</span> table2<span class="token punctuation">.</span>column2
<span class="token keyword">WHERE</span> 条件
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> emp<span class="token punctuation">.</span>EMPNO<span class="token punctuation">,</span> emp<span class="token punctuation">.</span>ENAME<span class="token punctuation">,</span> emp<span class="token punctuation">.</span>SAL<span class="token punctuation">,</span> dept<span class="token punctuation">.</span>DNAME 
<span class="token keyword">FROM</span> emp <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> dept <span class="token keyword">ON</span> emp<span class="token punctuation">.</span>DEPTNO <span class="token operator">=</span> dept<span class="token punctuation">.</span>DEPTNO
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>查询出 JOIN 左边表的全部数据查询出来，JOIN 右边的表不匹配的数据使用 NULL 来填充数据。</p> <h3 id="_6-3-右外连接查询"><a href="#_6-3-右外连接查询" class="header-anchor">#</a> 6.3 右外连接查询</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> table1<span class="token punctuation">.</span><span class="token keyword">column</span><span class="token punctuation">,</span> table2<span class="token punctuation">.</span><span class="token keyword">column</span>
<span class="token keyword">FROM</span> table1 <span class="token keyword">RIGHT</span> <span class="token punctuation">[</span><span class="token keyword">OUTER</span><span class="token punctuation">]</span> <span class="token keyword">JOIN</span> table2 <span class="token keyword">ON</span> table1<span class="token punctuation">.</span>column1 <span class="token operator">=</span> table2<span class="token punctuation">.</span>column2
<span class="token keyword">WHERE</span> 条件
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> emp<span class="token punctuation">.</span>EMPNO<span class="token punctuation">,</span> emp<span class="token punctuation">.</span>ENAME<span class="token punctuation">,</span> emp<span class="token punctuation">.</span>SAL<span class="token punctuation">,</span> dept<span class="token punctuation">.</span>DNAME <span class="token keyword">FROM</span> dept <span class="token keyword">RIGHT</span> <span class="token keyword">JOIN</span> emp <span class="token keyword">ON</span> emp<span class="token punctuation">.</span>DEPTNO <span class="token operator">=</span> dept<span class="token punctuation">.</span>DEPTNO
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>查询出 JOIN 右边表的全部数据查询出来，JOIN 左边的表不匹配的数据使用 NULL 来填充数据。</p> <h2 id="七、分组函数"><a href="#七、分组函数" class="header-anchor">#</a> 七、分组函数</h2> <h3 id="_7-1-函数分类"><a href="#_7-1-函数分类" class="header-anchor">#</a> 7.1 函数分类</h3> <ul><li>单行函数：将每条数据进行独立的计算，然后每条数据得到一条结果；</li> <li>多行函数：多条数据同时计算，最终得到一条结果数据。也称为聚集函数、分组函数，主要用于完成一些统计功能等等。</li></ul> <h3 id="_7-2-多行函数"><a href="#_7-2-多行函数" class="header-anchor">#</a> 7.2 多行函数</h3> <ul><li>COUNT()：查询表中的数据记录；</li> <li>AVG()：求出平均值；</li> <li>SUM()：求和；</li> <li>MAX()：求出最大值；</li> <li>MIN()：求出最小值。</li></ul> <p>注意：</p> <ul><li>统计函数忽略空值，可以使用 IFNULL， 因为是 NULL 不会影响汇总值，但会影响汇总数量；</li> <li>不能在 where 语句中使用分组函数。</li></ul> <p>练习：</p> <ul><li>查询所有员工每个月的平均工资及总工资</li> <li>查询月薪在 2000 以上的员工总人数</li> <li>查询员工最高工资和最低工资差距</li></ul> <h2 id="八、分组查询"><a href="#八、分组查询" class="header-anchor">#</a> 八、分组查询</h2> <h3 id="_8-1-什么是分组及其应用"><a href="#_8-1-什么是分组及其应用" class="header-anchor">#</a> 8.1 什么是分组及其应用</h3> <p>分组情况1：按照性别分组，男生一组，女生一组，之后可以统计男生和女生的数量；
分组情况2：按照年龄段分组，80 后一组，90 后一组；
分组情况3：按照籍贯分组，广东一组，湖南一组，江西一组。</p> <p><em><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/groupapp.png" alt=""></em></p> <h3 id="_8-2-分组语法"><a href="#_8-2-分组语法" class="header-anchor">#</a> 8.2 分组语法</h3> <p>在 MySQL 使用 GROUP BY 来实现分组。语法如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token punctuation">[</span><span class="token keyword">DISTINCT</span><span class="token punctuation">]</span> <span class="token operator">*</span><span class="token operator">|</span>分组字段<span class="token number">1</span> <span class="token punctuation">[</span>别名<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token punctuation">,</span> 分组字段<span class="token number">2</span> <span class="token punctuation">[</span>别名<span class="token punctuation">]</span> <span class="token punctuation">,</span>…<span class="token punctuation">]</span> <span class="token operator">|</span> 统计函数
<span class="token keyword">FROM</span> 表名称 <span class="token punctuation">[</span>别名<span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token punctuation">[</span>表名称 <span class="token punctuation">[</span>别名<span class="token punctuation">]</span> <span class="token punctuation">,</span> …<span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">WHERE</span> 条件<span class="token punctuation">(</span>s<span class="token punctuation">)</span><span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">GROUP</span> <span class="token keyword">BY</span> 分组字段<span class="token number">1</span> <span class="token punctuation">[</span><span class="token punctuation">,</span> 分组字段<span class="token number">2</span> <span class="token punctuation">,</span>…<span class="token punctuation">]</span><span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> 排序字段 <span class="token keyword">ASC</span> <span class="token operator">|</span> <span class="token keyword">DESC</span> <span class="token punctuation">[</span><span class="token punctuation">,</span> 排序字段 <span class="token keyword">ASC</span> <span class="token operator">|</span> <span class="token keyword">DESC</span><span class="token punctuation">]</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>分组效果：</p> <ul><li>使用 GROUP BY 子句将表分成小组；</li> <li>结果集隐式按升序排列，如果需要改变排序方式可以使用 ORDER BY 子句。</li></ul> <p>练习：</p> <ul><li>按照职位分组，求出每个职位的最高和最低工资</li> <li>查询出每一个部门员工的平均奖金</li></ul> <h3 id="_8-3-使用分组注意"><a href="#_8-3-使用分组注意" class="header-anchor">#</a> 8.3 使用分组注意</h3> <ul><li>SELECT 子句出现的字段，要么是统计函数，要不出现在 GROUP BY 子句中，否则不合理（整体与个体）；</li> <li>在GROUP BY 子句中出现的字段，可以不出现在 SELECT 列表中；</li> <li>统计函数可以单独使用，SQL 中可以没有 GROUP BY 子句；</li> <li>在 GROUP BY 子句中，可以按单列进行分组，也可以在多列上进行分组，多列分组就是按照多个字段的组合进行分组，最终的结果也会按照分组字段进行排序显示。</li></ul> <p>练习：</p> <ul><li>查询出每一个部门员工的平均工资</li> <li>查询各个部门和岗位的平均工资</li></ul> <h3 id="_8-4-分组限定"><a href="#_8-4-分组限定" class="header-anchor">#</a> 8.4 分组限定</h3> <ul><li><p>不能在 WHERE 子句中对分组限定，限制组须使用 HAVING 子句；</p></li> <li><p>不能在 WHERE 子句中使用统计函数，而在 HAVING 子句可使用统计函数。</p> <p>WHERE 分组之前过滤</p> <p>HAVING 过滤</p></li></ul> <p>练习：</p> <ul><li>查询部门平均工资高于 2000 的部门及其平均工资</li> <li>查询在 80, 81, 82 年各进公司多少人</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token keyword">YEAR</span><span class="token punctuation">(</span>HIREDATE<span class="token punctuation">)</span> y<span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>EMPNO<span class="token punctuation">)</span> 
<span class="token keyword">FROM</span> emp 
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token keyword">YEAR</span><span class="token punctuation">(</span>HIREDATE<span class="token punctuation">)</span> 
<span class="token keyword">HAVING</span> y <span class="token operator">BETWEEN</span> <span class="token string">'1980'</span> <span class="token operator">AND</span> <span class="token string">'1982'</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><ul><li>查询各个管理人员下员工的平均工资，其中最低工资不能低于 1300，不计算老板</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> MGR<span class="token punctuation">,</span> <span class="token function">AVG</span><span class="token punctuation">(</span>SAL<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">MIN</span><span class="token punctuation">(</span>SAL<span class="token punctuation">)</span> <span class="token keyword">AS</span> minsal 
<span class="token keyword">FROM</span> emp 
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> MGR 
<span class="token keyword">HAVING</span> MGR <span class="token operator">IS</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token operator">AND</span> minsal <span class="token operator">&gt;=</span> <span class="token number">1300</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h2 id="九、单行函数"><a href="#九、单行函数" class="header-anchor">#</a> 九、单行函数</h2> <h3 id="_9-1-日期函数"><a href="#_9-1-日期函数" class="header-anchor">#</a> 9.1 日期函数</h3> <ul><li>NOW()：获取当前时间；</li> <li>DAY(date)：获取日期中的天数，范围是从 1 到 31；</li> <li>HOUR(time)：返回 time 对应的小时数，范围是从 0 到 23；</li> <li>MINUTE(time)：返回 time 对应的分钟数，范围是从 0 到 59；</li> <li>MONTH(date)：返回 date 对应的月份，范围时从 1 到 12；</li> <li>YEAR(date)：返回 date 对应的年份，范围是从 1000 到 9999；</li> <li>LAST_DAY(date)：获取一个日期或日期时间值，返回该月最后一天对应的值。</li></ul> <h3 id="_9-2-日期转换为字符串"><a href="#_9-2-日期转换为字符串" class="header-anchor">#</a> 9.2 日期转换为字符串</h3> <p>DATE_FORMAT(date, format)：把日期转换为字符串。其中 format 中常见格式化符号如下（更多参见 MySQL 5.5 中文参考手册 549 页）：</p> <p><em><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/formatsign.png" alt=""></em></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> DATE_FORMAT<span class="token punctuation">(</span><span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">'%Y-%m-%d'</span><span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h2 id="十、子查询"><a href="#十、子查询" class="header-anchor">#</a> 十、子查询</h2> <h3 id="_10-1-定义和作用"><a href="#_10-1-定义和作用" class="header-anchor">#</a> 10.1 定义和作用</h3> <p>子查询指的就是在一个查询之中嵌套了其他的若干查询。</p> <p>在使用 SELECT 语句查询数据时，有时候会遇到这样的情况，在 WHERE 查询条件中的限制条件不是一个确定的值，而是一个来自于另一个查询的结果（比如查询大于平均工资的员工）。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> select_list
<span class="token keyword">FROM</span>	<span class="token keyword">table</span>
<span class="token keyword">WHERE</span>	expr operator <span class="token punctuation">(</span><span class="token keyword">SELECt</span> select_list <span class="token keyword">FROM</span> <span class="token keyword">table</span><span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>注意：</p> <ul><li>子查询一般出现在 FROM 和 WHERE 子句中；</li> <li>子查询要使用圆括号括起来；</li> <li>将子查询放在比较运算符的右边（增强可读性）；</li> <li>子查询在主查询前执行一次，主查询使用子查询的结果；但不宜嵌套过多。</li></ul> <h3 id="_10-2-分类"><a href="#_10-2-分类" class="header-anchor">#</a> 10.2 分类</h3> <p>按查询结果分类：</p> <ul><li>单行单列：只包含一个字段的查询，返回的查询结果也只包含一行数据；</li> <li>多行单列：只包含了一个字段，但返回的查询结果可能多行或者零行；</li> <li>多行多列：包含多个字段的返回，查询结果可能是单行或者多行，好比是一张表。</li></ul> <h3 id="_10-3-单行单列"><a href="#_10-3-单行单列" class="header-anchor">#</a> 10.3 单行单列</h3> <p>一般用于 WHERE 之后的子查询，子查询结果是一行一列记录。
使用单行记录比较运算符：=、&gt;、&gt;=、&lt;、&lt;=、&lt;&gt;。</p> <p>练习：</p> <ul><li>查询出工资比 MARTIN 还要高的全部雇员信息</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> emp <span class="token keyword">WHERE</span> sal <span class="token operator">&gt;</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> sal <span class="token keyword">FROM</span> emp <span class="token keyword">WHERE</span> ename <span class="token operator">=</span> <span class="token string">'MARTIN'</span><span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>查询平均工资高于公司平均工资的部门信息</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> e<span class="token punctuation">.</span>deptno<span class="token punctuation">,</span> d<span class="token punctuation">.</span>dname<span class="token punctuation">,</span> <span class="token function">AVG</span><span class="token punctuation">(</span>sal<span class="token punctuation">)</span> 
<span class="token keyword">FROM</span> emp e <span class="token keyword">JOIN</span> dept d  <span class="token keyword">ON</span> e<span class="token punctuation">.</span>deptno <span class="token operator">=</span> d<span class="token punctuation">.</span>deptno 
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> deptno <span class="token keyword">HAVING</span> <span class="token function">AVG</span><span class="token punctuation">(</span>sal<span class="token punctuation">)</span> <span class="token operator">&gt;=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>sal<span class="token punctuation">)</span> <span class="token keyword">FROM</span> emp<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h3 id="_10-4-多行单列"><a href="#_10-4-多行单列" class="header-anchor">#</a> 10.4 多行单列</h3> <p>一般也用于 WHERE 子句中，子查询结果只有一列，但是有多行。使用多行比较运算符：</p> <ul><li><code>IN</code>：与列表中的任意一个值相等</li> <li><code>ANY</code>：与子查询返回的任意一个值比较
<ul><li><code>=ANY</code>：此时和 IN 操作符相同</li> <li><code>&gt;ANY</code>：大于子查询中最小的数据</li> <li><code>&lt;ANY</code>：小于子查询中最大的数据</li></ul></li> <li><code>ALL</code>：与子查询返回的每一个值比较
<ul><li><code>&gt;ALL</code>：大于子查询中最大的数据</li> <li><code>&lt;ALL</code>：小于子查询中最小的数据</li></ul></li></ul> <p>练习：</p> <ul><li>查询工资等于部门经理（职位是 MANAGER）的员工信息。</li></ul> <h3 id="_10-5-多行多列"><a href="#_10-5-多行多列" class="header-anchor">#</a> 10.5 多行多列</h3> <p>子查询的结果是多行多列，一般会把子查询返回的结果当成一个临时表，接着在临时表上继续查询或者连接查询；</p> <p>注意：多行多列的子查询返回的结果必须要设置一个临时表名。</p> <p>练习：</p> <ul><li>查询出每个部门的编号、名称、部门人数、平均工资</li></ul> <p>可以先把每一个部门的编号，总人数，平均工资先查询出来：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> deptno dno<span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>empno<span class="token punctuation">)</span> count<span class="token punctuation">,</span> <span class="token function">AVG</span><span class="token punctuation">(</span>sal<span class="token punctuation">)</span> avg  <span class="token keyword">FROM</span> emp <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> dno
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>再 和 dept 表联合查询部门名称：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span>  dept<span class="token punctuation">.</span>deptno<span class="token punctuation">,</span> <span class="token keyword">temp</span><span class="token punctuation">.</span>count<span class="token punctuation">,</span> <span class="token keyword">temp</span><span class="token punctuation">.</span>avg  
<span class="token keyword">FROM</span> dept <span class="token keyword">JOIN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> deptno dno<span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>empno<span class="token punctuation">)</span> count<span class="token punctuation">,</span> <span class="token function">AVG</span><span class="token punctuation">(</span>sal<span class="token punctuation">)</span> avg  <span class="token keyword">FROM</span> emp <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> deptno<span class="token punctuation">)</span> <span class="token keyword">temp</span> <span class="token keyword">ON</span> dept<span class="token punctuation">.</span>deptno <span class="token operator">=</span> <span class="token keyword">temp</span><span class="token punctuation">.</span>dno
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h2 id="十一、小结与作业"><a href="#十一、小结与作业" class="header-anchor">#</a> 十一、小结与作业</h2> <h3 id="_11-1小结"><a href="#_11-1小结" class="header-anchor">#</a> 11.1小结</h3> <ul><li><p>理解数据库三范式。</p> <p>第一范式, 一个列不能有多个值, 一定要遵循;</p> <p>第二范式, 表要设置主键(可遵循可不遵循);</p> <p>第三范式, 一个表的列只能关联另外的表主键列(可遵循可不遵循).</p></li> <li><p>掌握使用单表查询。</p> <p>NULL: 数据库任何类型支持 NULL , 只要 NULL 参与运算结果都为 NULL,  NULL 不等于空格空 字符串</p> <p>要判断一个列的值是否为 NULL   IS NULL   IS NOT NULL</p></li></ul> <p>​       过滤条件值, 都可以用引号括起来</p> <p>​       排序列名或者别名不要用引号括起来, 否则排序 没有效果</p> <ul><li><p>理解多表查询的作用，并掌握内连接查询和左外连接查询的使用。</p> <p>解决查询数据分散在不同表中的需求</p> <p>内连接查询(只查询交集部分)</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> 列名<span class="token number">1</span><span class="token punctuation">,</span> 列名<span class="token number">2</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> 
<span class="token keyword">FROM</span> 表<span class="token number">1</span><span class="token punctuation">,</span> 表<span class="token number">2</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">WHERE</span> 消除笛卡尔积的条件 <span class="token operator">AND</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>

<span class="token keyword">SELECT</span> 列名<span class="token number">1</span><span class="token punctuation">,</span> 列名<span class="token number">2</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> 
<span class="token keyword">FROM</span> 表<span class="token number">1</span> 
<span class="token keyword">JOIN</span> 表<span class="token number">2</span> <span class="token keyword">ON</span> 消除笛卡尔积的条件<span class="token number">1</span>
<span class="token keyword">JOIN</span> 表<span class="token number">3</span> <span class="token keyword">ON</span> 消除笛卡尔积的条件<span class="token number">2</span>
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><p>左外连接查询(查询交集部分+左边独有数据)</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> 列名<span class="token number">1</span><span class="token punctuation">,</span> 列名<span class="token number">2</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> 
<span class="token keyword">FROM</span> 表<span class="token number">1</span> 
<span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> 表<span class="token number">2</span> <span class="token keyword">ON</span> 消除笛卡尔积的条件<span class="token number">1</span>
<span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> 表<span class="token number">3</span> <span class="token keyword">ON</span> 消除笛卡尔积的条件<span class="token number">2</span>
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>右外连接查询(查询交集部分+右边独有数据)</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> 列名<span class="token number">1</span><span class="token punctuation">,</span> 列名<span class="token number">2</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> 
<span class="token keyword">FROM</span> 表<span class="token number">1</span> 
<span class="token keyword">RIGHT</span> <span class="token keyword">JOIN</span> 表<span class="token number">2</span> <span class="token keyword">ON</span> 消除笛卡尔积的条件<span class="token number">1</span>
<span class="token keyword">RIGHT</span> <span class="token keyword">JOIN</span> 表<span class="token number">3</span> <span class="token keyword">ON</span> 消除笛卡尔积的条件<span class="token number">2</span>
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div></li> <li><p>掌握使用分组函数。</p> <p>COUNT 函数中要不写 * 要不写主键名</p></li> <li><p>掌握使用分组查询，并注意查询列可以有哪些比较合理。</p> <p>解决报表(折线图, 饼状图, 柱状图等)查询的需求, 提供报表显示数据</p> <div class="language- line-numbers-mode"><pre class="language-text"><code>SELECT 列名1, 列名2, 统计函数(列名3), ... 
FROM 表
WHERE 分组之前过滤条件
GROUP BY 列名1, 列名2, ...
HAVING 分组之后过滤条件
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>能代表这组数据共有特点就选用那个列, 或者这个列出现统计函数中</p></li> <li><p>了解单行函数。</p></li> <li><p>掌握子查询的使用</p> <p>解决条件的值需要通过sql 查询出来的时候, 我们就会使用子查询</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> 列名<span class="token number">1</span><span class="token punctuation">,</span> 列名<span class="token number">2</span><span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">FROM</span> 表
<span class="token keyword">WHERE</span> 列名 运行符 <span class="token punctuation">(</span>子查询<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>运算符</p> <ul><li>子查询返回是单行单列  使用 =  &gt;=  &gt;  &lt; &lt;= !=</li> <li>子查询返回是多行单列  使用 IN</li></ul></li></ul> <h3 id="_11-2-作业"><a href="#_11-2-作业" class="header-anchor">#</a> 11.2 作业</h3> <p>大家自己建库，导入给 mysqlplus.sql 文件，完成以下 SQL 练习：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">/*
Navicat MySQL Data Transfer
Date: 2015-11-23 23:22:16
*/</span>

<span class="token keyword">SET</span> FOREIGN_KEY_CHECKS<span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span>

<span class="token comment">-- ----------------------------</span>
<span class="token comment">-- Table structure for `dept`</span>
<span class="token comment">-- ----------------------------</span>
<span class="token keyword">DROP</span> <span class="token keyword">TABLE</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> <span class="token identifier"><span class="token punctuation">`</span>dept<span class="token punctuation">`</span></span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token identifier"><span class="token punctuation">`</span>dept<span class="token punctuation">`</span></span> <span class="token punctuation">(</span>
  <span class="token identifier"><span class="token punctuation">`</span>DEPTNO<span class="token punctuation">`</span></span> <span class="token keyword">bigint</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">COMMENT</span> <span class="token string">'表示部门编号，由两位数字所组成'</span><span class="token punctuation">,</span>
  <span class="token identifier"><span class="token punctuation">`</span>DNAME<span class="token punctuation">`</span></span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">14</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'部门名称，最多由14个字符所组成'</span><span class="token punctuation">,</span>
  <span class="token identifier"><span class="token punctuation">`</span>LOC<span class="token punctuation">`</span></span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">13</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'部门所在的位置'</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">InnoDB</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8<span class="token punctuation">;</span>

<span class="token comment">-- ----------------------------</span>
<span class="token comment">-- Records of dept</span>
<span class="token comment">-- ACCOUNTING:财务部</span>
<span class="token comment">-- RESEARCH:调研部</span>
<span class="token comment">-- SALES:销售部</span>
<span class="token comment">-- OPERATIONS:运营部</span>
<span class="token comment">-- ----------------------------</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>dept<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'10'</span><span class="token punctuation">,</span> <span class="token string">'ACCOUNTING'</span><span class="token punctuation">,</span> <span class="token string">'NEW YORK'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>dept<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'20'</span><span class="token punctuation">,</span> <span class="token string">'RESEARCH'</span><span class="token punctuation">,</span> <span class="token string">'DALLAS'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>dept<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'30'</span><span class="token punctuation">,</span> <span class="token string">'SALES'</span><span class="token punctuation">,</span> <span class="token string">'CHICAGO'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>dept<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'40'</span><span class="token punctuation">,</span> <span class="token string">'OPERATIONS'</span><span class="token punctuation">,</span> <span class="token string">'BOSTON'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- ----------------------------</span>
<span class="token comment">-- Table structure for `emp`</span>
<span class="token comment">-- ----------------------------</span>
<span class="token keyword">DROP</span> <span class="token keyword">TABLE</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> <span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span> <span class="token punctuation">(</span>
  <span class="token identifier"><span class="token punctuation">`</span>EMPNO<span class="token punctuation">`</span></span> <span class="token keyword">bigint</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">)</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">COMMENT</span> <span class="token string">'雇员的编号，由四位数字所组成'</span><span class="token punctuation">,</span>
  <span class="token identifier"><span class="token punctuation">`</span>ENAME<span class="token punctuation">`</span></span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'雇员的姓名，由10位字符所组成'</span><span class="token punctuation">,</span>
  <span class="token identifier"><span class="token punctuation">`</span>JOB<span class="token punctuation">`</span></span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">9</span><span class="token punctuation">)</span>  <span class="token keyword">COMMENT</span> <span class="token string">'雇员的职位'</span><span class="token punctuation">,</span>
  <span class="token identifier"><span class="token punctuation">`</span>MGR<span class="token punctuation">`</span></span> <span class="token keyword">bigint</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">)</span>  <span class="token keyword">COMMENT</span> <span class="token string">'雇员对应的领导编号，领导也是雇员'</span><span class="token punctuation">,</span>
  <span class="token identifier"><span class="token punctuation">`</span>HIREDATE<span class="token punctuation">`</span></span> <span class="token keyword">date</span> <span class="token keyword">COMMENT</span> <span class="token string">'雇员的雇佣日期'</span><span class="token punctuation">,</span>
  <span class="token identifier"><span class="token punctuation">`</span>SAL<span class="token punctuation">`</span></span> <span class="token keyword">double</span><span class="token punctuation">(</span><span class="token number">7</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'基本工资，其中有两位小数，五位整数，一共是七位'</span><span class="token punctuation">,</span>
  <span class="token identifier"><span class="token punctuation">`</span>COMM<span class="token punctuation">`</span></span> <span class="token keyword">double</span><span class="token punctuation">(</span><span class="token number">7</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'奖金，佣金(销售才有)'</span><span class="token punctuation">,</span>
  <span class="token identifier"><span class="token punctuation">`</span>DEPTNO<span class="token punctuation">`</span></span> <span class="token keyword">bigint</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'雇员所在的部门编号'</span>
<span class="token punctuation">)</span><span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">InnoDB</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8<span class="token punctuation">;</span>

<span class="token comment">-- ----------------------------</span>
<span class="token comment">-- Records of emp</span>
<span class="token comment">-- CLERK:职员</span>
<span class="token comment">-- SALESMAN:销售员</span>
<span class="token comment">-- MANAGER:部门经理</span>
<span class="token comment">-- ANALYST:分析员</span>
<span class="token comment">-- PRESIDENT:总裁</span>
<span class="token comment">-- ----------------------------</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'7369'</span><span class="token punctuation">,</span> <span class="token string">'SMITH'</span><span class="token punctuation">,</span> <span class="token string">'CLERK'</span><span class="token punctuation">,</span> <span class="token string">'7902'</span><span class="token punctuation">,</span> <span class="token string">'1980-12-17'</span><span class="token punctuation">,</span> <span class="token string">'800.00'</span><span class="token punctuation">,</span> <span class="token boolean">null</span><span class="token punctuation">,</span> <span class="token string">'20'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'7499'</span><span class="token punctuation">,</span> <span class="token string">'ALLEN'</span><span class="token punctuation">,</span> <span class="token string">'SALESMAN'</span><span class="token punctuation">,</span> <span class="token string">'7698'</span><span class="token punctuation">,</span> <span class="token string">'1981-02-20'</span><span class="token punctuation">,</span> <span class="token string">'1600.00'</span><span class="token punctuation">,</span> <span class="token string">'300.00'</span><span class="token punctuation">,</span> <span class="token string">'30'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'7521'</span><span class="token punctuation">,</span> <span class="token string">'WARD'</span><span class="token punctuation">,</span> <span class="token string">'SALESMAN'</span><span class="token punctuation">,</span> <span class="token string">'7698'</span><span class="token punctuation">,</span> <span class="token string">'1981-02-22'</span><span class="token punctuation">,</span> <span class="token string">'1250.00'</span><span class="token punctuation">,</span> <span class="token string">'500.00'</span><span class="token punctuation">,</span> <span class="token string">'30'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'7566'</span><span class="token punctuation">,</span> <span class="token string">'JONES'</span><span class="token punctuation">,</span> <span class="token string">'MANAGER'</span><span class="token punctuation">,</span> <span class="token string">'7839'</span><span class="token punctuation">,</span> <span class="token string">'1981-04-02'</span><span class="token punctuation">,</span> <span class="token string">'2975.00'</span><span class="token punctuation">,</span> <span class="token boolean">null</span><span class="token punctuation">,</span> <span class="token string">'20'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'7654'</span><span class="token punctuation">,</span> <span class="token string">'MARTIN'</span><span class="token punctuation">,</span> <span class="token string">'SALESMAN'</span><span class="token punctuation">,</span> <span class="token string">'7698'</span><span class="token punctuation">,</span> <span class="token string">'1981-09-28'</span><span class="token punctuation">,</span> <span class="token string">'1250.00'</span><span class="token punctuation">,</span> <span class="token string">'1400.00'</span><span class="token punctuation">,</span> <span class="token string">'30'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'7698'</span><span class="token punctuation">,</span> <span class="token string">'BLAKE'</span><span class="token punctuation">,</span> <span class="token string">'MANAGER'</span><span class="token punctuation">,</span> <span class="token string">'7839'</span><span class="token punctuation">,</span> <span class="token string">'1981-05-01'</span><span class="token punctuation">,</span> <span class="token string">'2850.00'</span><span class="token punctuation">,</span> <span class="token boolean">null</span><span class="token punctuation">,</span> <span class="token string">'30'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'7782'</span><span class="token punctuation">,</span> <span class="token string">'CLARK'</span><span class="token punctuation">,</span> <span class="token string">'MANAGER'</span><span class="token punctuation">,</span> <span class="token string">'7839'</span><span class="token punctuation">,</span> <span class="token string">'1981-06-09'</span><span class="token punctuation">,</span> <span class="token string">'2450.00'</span><span class="token punctuation">,</span> <span class="token boolean">null</span><span class="token punctuation">,</span> <span class="token string">'10'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'7788'</span><span class="token punctuation">,</span> <span class="token string">'SCOTT'</span><span class="token punctuation">,</span> <span class="token string">'ANALYST'</span><span class="token punctuation">,</span> <span class="token string">'7566'</span><span class="token punctuation">,</span> <span class="token string">'1987-04-19'</span><span class="token punctuation">,</span> <span class="token string">'3000.00'</span><span class="token punctuation">,</span> <span class="token boolean">null</span><span class="token punctuation">,</span> <span class="token string">'20'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'7839'</span><span class="token punctuation">,</span> <span class="token string">'KING'</span><span class="token punctuation">,</span> <span class="token string">'PRESIDENT'</span><span class="token punctuation">,</span> <span class="token boolean">null</span><span class="token punctuation">,</span> <span class="token string">'1981-11-17'</span><span class="token punctuation">,</span> <span class="token string">'5000.00'</span><span class="token punctuation">,</span> <span class="token boolean">null</span><span class="token punctuation">,</span> <span class="token string">'10'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'7844'</span><span class="token punctuation">,</span> <span class="token string">'TURNER'</span><span class="token punctuation">,</span> <span class="token string">'SALESMAN'</span><span class="token punctuation">,</span> <span class="token string">'7698'</span><span class="token punctuation">,</span> <span class="token string">'1981-09-08'</span><span class="token punctuation">,</span> <span class="token string">'1500.00'</span><span class="token punctuation">,</span> <span class="token string">'0.00'</span><span class="token punctuation">,</span> <span class="token string">'30'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'7876'</span><span class="token punctuation">,</span> <span class="token string">'ADAMS'</span><span class="token punctuation">,</span> <span class="token string">'CLERK'</span><span class="token punctuation">,</span> <span class="token string">'7788'</span><span class="token punctuation">,</span> <span class="token string">'1987-05-23'</span><span class="token punctuation">,</span> <span class="token string">'1100.00'</span><span class="token punctuation">,</span> <span class="token boolean">null</span><span class="token punctuation">,</span> <span class="token string">'20'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'7900'</span><span class="token punctuation">,</span> <span class="token string">'JAMES'</span><span class="token punctuation">,</span> <span class="token string">'CLERK'</span><span class="token punctuation">,</span> <span class="token string">'7698'</span><span class="token punctuation">,</span> <span class="token string">'1981-12-03'</span><span class="token punctuation">,</span> <span class="token string">'950.00'</span><span class="token punctuation">,</span> <span class="token boolean">null</span><span class="token punctuation">,</span> <span class="token string">'30'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'7902'</span><span class="token punctuation">,</span> <span class="token string">'FORD'</span><span class="token punctuation">,</span> <span class="token string">'ANALYST'</span><span class="token punctuation">,</span> <span class="token string">'7566'</span><span class="token punctuation">,</span> <span class="token string">'1981-12-03'</span><span class="token punctuation">,</span> <span class="token string">'3000.00'</span><span class="token punctuation">,</span> <span class="token boolean">null</span><span class="token punctuation">,</span> <span class="token string">'20'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'7934'</span><span class="token punctuation">,</span> <span class="token string">'MILLER'</span><span class="token punctuation">,</span> <span class="token string">'CLERK'</span><span class="token punctuation">,</span> <span class="token string">'7782'</span><span class="token punctuation">,</span> <span class="token string">'1982-01-23'</span><span class="token punctuation">,</span> <span class="token string">'1300.00'</span><span class="token punctuation">,</span> <span class="token boolean">null</span><span class="token punctuation">,</span> <span class="token string">'10'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- ----------------------------</span>
<span class="token comment">-- Table structure for `salgrade`</span>
<span class="token comment">-- ----------------------------</span>
<span class="token keyword">DROP</span> <span class="token keyword">TABLE</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> <span class="token identifier"><span class="token punctuation">`</span>salgrade<span class="token punctuation">`</span></span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token identifier"><span class="token punctuation">`</span>salgrade<span class="token punctuation">`</span></span> <span class="token punctuation">(</span>
  <span class="token identifier"><span class="token punctuation">`</span>GRADE<span class="token punctuation">`</span></span> <span class="token keyword">bigint</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">COMMENT</span> <span class="token string">'工资等级'</span><span class="token punctuation">,</span>
  <span class="token identifier"><span class="token punctuation">`</span>LOSAL<span class="token punctuation">`</span></span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>  <span class="token keyword">COMMENT</span> <span class="token string">'此等级的最低工资'</span><span class="token punctuation">,</span>
  <span class="token identifier"><span class="token punctuation">`</span>HISAL<span class="token punctuation">`</span></span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>  <span class="token keyword">COMMENT</span> <span class="token string">'此等级的最高工资'</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">InnoDB</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8<span class="token punctuation">;</span>

<span class="token comment">-- ----------------------------</span>
<span class="token comment">-- Records of salgrade</span>
<span class="token comment">-- ----------------------------</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>salgrade<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'1'</span><span class="token punctuation">,</span> <span class="token string">'700'</span><span class="token punctuation">,</span> <span class="token string">'1200'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>salgrade<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'2'</span><span class="token punctuation">,</span> <span class="token string">'1201'</span><span class="token punctuation">,</span> <span class="token string">'1400'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>salgrade<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'3'</span><span class="token punctuation">,</span> <span class="token string">'1401'</span><span class="token punctuation">,</span> <span class="token string">'2000'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>salgrade<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'4'</span><span class="token punctuation">,</span> <span class="token string">'2001'</span><span class="token punctuation">,</span> <span class="token string">'3000'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>salgrade<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'5'</span><span class="token punctuation">,</span> <span class="token string">'3001'</span><span class="token punctuation">,</span> <span class="token string">'9999'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br><span class="line-number">42</span><br><span class="line-number">43</span><br><span class="line-number">44</span><br><span class="line-number">45</span><br><span class="line-number">46</span><br><span class="line-number">47</span><br><span class="line-number">48</span><br><span class="line-number">49</span><br><span class="line-number">50</span><br><span class="line-number">51</span><br><span class="line-number">52</span><br><span class="line-number">53</span><br><span class="line-number">54</span><br><span class="line-number">55</span><br><span class="line-number">56</span><br><span class="line-number">57</span><br><span class="line-number">58</span><br><span class="line-number">59</span><br><span class="line-number">60</span><br><span class="line-number">61</span><br><span class="line-number">62</span><br><span class="line-number">63</span><br><span class="line-number">64</span><br><span class="line-number">65</span><br><span class="line-number">66</span><br><span class="line-number">67</span><br><span class="line-number">68</span><br><span class="line-number">69</span><br><span class="line-number">70</span><br><span class="line-number">71</span><br><span class="line-number">72</span><br><span class="line-number">73</span><br><span class="line-number">74</span><br><span class="line-number">75</span><br><span class="line-number">76</span><br><span class="line-number">77</span><br><span class="line-number">78</span><br><span class="line-number">79</span><br><span class="line-number">80</span><br><span class="line-number">81</span><br><span class="line-number">82</span><br><span class="line-number">83</span><br><span class="line-number">84</span><br><span class="line-number">85</span><br><span class="line-number">86</span><br></div></div><ul><li>查询所有员工信息。</li> <li>查询每个员工的编号、姓名、职位。</li> <li>查询所有部门信息。</li> <li>查询所有有员工的部门编号。</li> <li>查询有员工的部门和职位。</li> <li>查询所有员工的年薪。</li> <li>查询所有员工的年薪（使用别名）。</li> <li>查询所有员工的年薪((月薪 + 奖金) * 12) 。</li> <li>查询有奖金的员工信息。</li> <li>查询公司的老板。</li> <li>查询出基本工资高于 1500 的所有员工信息。</li> <li>查询名字叫 SCOTT 的员工所从事的工作。</li> <li>查询 1981 年入职的员工信息。</li> <li>查询年薪小于 3W 的员工。</li> <li>查询所有不是销售人员的员工信息。</li> <li>查询工资在 2000-3000 之间的员工信息。</li> <li>查询 1981 年入职的员工。</li> <li>查询工资为 800 或 1600 或 3000 的员工。</li> <li>查询出所有雇员姓名是以 A 开头的全部雇员信息。</li> <li>查询出雇员姓名第二个字母是 M 的全部雇员信息。</li> <li>查询出雇员姓名任意位置上包含字母 A 的全部雇员信息。</li> <li>查询姓名中有 e 或者 a 的员工姓名。</li> <li>查询工资在 1500~3000 之间的全部员工信息。</li> <li>查询工资不在 2000-3000 之间的员工信息。</li> <li>查询工资不为 800 或 1600 或 3000 的员工。</li> <li>查询出职位是办事员 (CLERK) 或者是销售人员 (SALESMAN) 的全部信息，且工资在 1000 以上。</li> <li>查询所有员工信息，按照工资排序。</li> <li>查询所有员工信息，按照年薪降序排序。</li> <li>查询所有员工信息，按照部门和年薪降序排序。</li> <li>查询员工编号，员工名称，员工所属部门的编号和名称。</li> <li>查询员工的姓名，工资，所在部门的名称，以及工资的等级。</li> <li>查询所有员工每个月的平均工资及总工资。</li> <li>查询月薪在 2000 以上的员工总人数。</li> <li>查询员工最高工资和最低工资差距。</li> <li>按照职位分组，求出每个职位的最高和最低工资。</li> <li>查询出每一个部门员工的平均奖金。</li> <li>查询出每一个部门员工的平均工资。</li> <li>查询各个部门和岗位的平均工资。</li> <li>查询部门平均工资高于 2000 的部门及其平均工资。</li> <li>查询在 80，81，82 年各进公司多少人。</li> <li>查询各个管理人员下员工的平均工资，其中最低工资不能低于 1300，不计算老板。</li> <li>查询出工资比 MARTIN 还要高的全部雇员信息。</li> <li>查询平均工资高于公司平均工资的部门信息。</li> <li>查询工资等于部门经理（职位是 MANAGER）的员工信息。</li> <li>查询出每个部门的编号、名称、部门人数、平均工资。</li></ul></div></div>  <div class="page-edit"><!----> <div class="tags"><a href="/tags/?tag=MySQL" title="标签">#MySQL</a></div> <!----></div> <div class="page-nav-wapper"><div class="page-nav-centre-wrap"><a href="/pages/71d587/" class="page-nav-centre page-nav-centre-prev"><div class="tooltip">Maven</div></a> <a href="/pages/712f3f/" class="page-nav-centre page-nav-centre-next"><div class="tooltip">MyBatis加强</div></a></div> <div class="page-nav"><p class="inner"><span class="prev">
        ←
        <a href="/pages/71d587/" class="prev">Maven</a></span> <span class="next"><a href="/pages/712f3f/">MyBatis加强</a>→
      </span></p></div></div></div> <div class="article-list"><div class="article-title"><a href="/archives/" class="iconfont icon-bi">最近更新</a></div> <div class="article-wrapper"><dl><dd>01</dd> <dt><a href="/pages/0c07b2/"><div>
            IT杂货铺
            <!----></div></a> <span class="date">03-23</span></dt></dl><dl><dd>02</dd> <dt><a href="/pages/705b35/"><div>
            人事百问
            <!----></div></a> <span class="date">03-22</span></dt></dl><dl><dd>03</dd> <dt><a href="/pages/78eb56/"><div>
            MyBatis-Plus
            <!----></div></a> <span class="date">03-12</span></dt></dl> <dl><dd></dd> <dt><a href="/archives/" class="more">更多文章&gt;</a></dt></dl></div></div></main></div> <div class="footer"><div class="icons"><a href="mailto:langfeiyes@163.com" title="发邮件" target="_blank" class="iconfont icon-youjian"></a><a href="https://github.com/langfeiyes" title="GitHub" target="_blank" class="iconfont icon-github"></a><a href="https://music.163.com/#/playlist?id=755597173" title="听音乐" target="_blank" class="iconfont icon-erji"></a></div> 
  Theme by
  <a href="https://github.com/xugaoyi/vuepress-theme-vdoing" target="_blank" title="本站主题">Vdoing</a> 
    | Copyright © 2024-2024
    <span><a href="https://beian.miit.gov.cn/" target="_blank">粤ICP备18007927号-1</a></span></div> <div class="buttons"><div title="返回顶部" class="button blur go-to-top iconfont icon-fanhuidingbu" style="display:none;"></div> <div title="去评论" class="button blur go-to-comment iconfont icon-pinglun" style="display:none;"></div> <div title="主题模式" class="button blur theme-mode-but iconfont icon-zhuti"><ul class="select-box" style="display:none;"><li class="iconfont icon-zidong">
          跟随系统
        </li><li class="iconfont icon-rijianmoshi">
          浅色模式
        </li><li class="iconfont icon-yejianmoshi">
          深色模式
        </li><li class="iconfont icon-yuedu">
          阅读模式
        </li></ul></div></div> <!----> <!----> <!----></div><div class="global-ui"><div></div></div></div>
    <script src="/assets/js/app.a2a00aa5.js" defer></script><script src="/assets/js/2.256f807a.js" defer></script><script src="/assets/js/47.edf3ddf8.js" defer></script>
  </body>
</html>
